* TAB 7: HOW DID EMPLOYMENT CHANGE by SECTOR? 

// NOTE: the output of this table is an excel-file. It needs to formatted and exported to latex manually

version 16.1

clear all
cap log close 
cap clear matrix
set more off
graph drop _all


cd "$mypathRR/Datasets/employment"


* Read data *

import excel using "BZ-Wirtschaftsabteilungen.xlsx", ///
sheet("FTE") cellrange(A3:F82840) firstrow clear
destring NOGA6, gen(noga6)

preserve
import excel using "BZ-Wirtschaftsabteilungen.xlsx", ///
sheet("total") cellrange(A3:F82840) firstrow clear
destring NOGA6, gen(noga6)

tempfile total
save "`total'"
restore

merge 1:1 year cant noga6 using "`total'", nogen

// collapse at 2 digit NOGA
gen noga2 = int(noga6/10000)
collapse  (sum) FTE (sum) jobs, by(year cant noga2)


// attach the totals

preserve
import excel using "BZ-Total_VZE.xlsx", ///
cellrange(A3:F111) sheet("data") firstrow clear
drop kanton
rename FTE FTE_total
rename jobs jobs_total
tempfile bz
save "`bz'"


gen noga2=0
rename FTE_total FTE
rename jobs_total jobs
drop quelle
tempfile nogatotal
save "`nogatotal'"

restore
append using "`nogatotal'"

merge m:1 year cant using "`bz'"
drop if _merge ==1
drop _merge



// gen shares
foreach var in FTE jobs {
gen `var'_share = `var' / `var'_total
}


run "$mypathRR/Resources/labels_NOGA.do"
label val noga2 NOGAen

gen noga1 = .
replace noga1 = 0 if noga2== 0

replace noga1 = 1 if noga2>= 1 & noga2<= 3
replace noga1 = 2 if noga2>= 5 & noga2<= 9
replace noga1 = 3 if noga2>= 10 & noga2<= 33
replace noga1 = 4 if noga2>= 35 & noga2<= 35
replace noga1 = 5 if noga2>= 36 & noga2<= 39
replace noga1 = 6 if noga2>= 41 & noga2<= 43
replace noga1 = 7 if noga2>= 45 & noga2<= 47
replace noga1 = 8 if noga2>= 49 & noga2<= 53
replace noga1 = 9 if noga2>= 55 & noga2<= 56
replace noga1 = 10 if noga2>= 58 & noga2<= 63
replace noga1 = 11 if noga2>= 64 & noga2<= 66
replace noga1 = 12 if noga2>= 68 & noga2<= 68
replace noga1 = 13 if noga2>= 69 & noga2<= 75
replace noga1 = 14 if noga2>= 77 & noga2<= 82
replace noga1 = 15 if noga2>= 84 & noga2<= 84
replace noga1 = 16 if noga2>= 85 & noga2<= 85
replace noga1 = 17 if noga2>= 86 & noga2<= 88
replace noga1 = 18 if noga2>= 90 & noga2<= 93
replace noga1 = 19 if noga2>= 94 & noga2<= 96
replace noga1 = 20 if noga2>= 97 & noga2<= 98
replace noga1 = 21 if noga2>= 99 & noga2<= 99
label val noga1 NOGA1en

collapse  (sum) FTE (sum) jobs, by(year cant noga1)
merge m:1 year cant using "`bz'"

// gen shares
foreach var in FTE jobs {
gen `var'_share = `var' / `var'_total
}

// gen growth
bys cant noga1 (year): gen FTE_gr = FTE/FTE[_n-1]-1
sort year cant FTE_gr
bys cant noga1 (year): gen FTEshare_gr = FTE_share/FTE_share[_n-1]-1


* LABEL EVERYTHING* 
label var noga1 "Sector"
label var FTE "FTE jobs"
label var FTE_gr "Growth in FTE jobs (2005-2008)"
label var FTE_share "Sector share in FTE employment"
label var FTEshare_gr "Growth in sector share in FTE employment"

label var jobs "Jobs"
label var jobs_share "Sector share in employment"



********************************************************************************
* MAKE TABLES *
********************************************************************************
cd "$mypathRR/Results/"
// To make a table with FTE growth
preserve
keep year cant FTE_gr FTE FTE_share FTEshare_gr noga1
reshape wide   FTE_gr FTE FTE_share FTEshare_gr, i(year noga1) j(cant)
label val noga1 NOGA1en
local lab0 "CH"
local lab1 "ZH"
local lab2 "BE"
local lab3 "LU"
local lab4 "UR"
local lab5 "SZ"
local lab6 "OW"
local lab7 "NW"
local lab8 "GL"
local lab9 "ZG"
local lab10 "FR"
local lab11 "SO"
local lab12 "BS"
local lab13 "BL"
local lab14 "SH"
local lab15 "AR"
local lab16 "AI"
local lab17 "SG"
local lab18 "GR"
local lab19 "AG"
local lab20 "TG"
local lab21 "TI"
local lab22 "VD"
local lab23 "VS"
local lab24 "NE"
local lab25 "GE"
local lab26 "JU"

forval n=0/26 {
label var FTE_gr`n' `lab`n''
label var FTE`n' `lab`n''
label var FTE_share`n' `lab`n''
label var FTEshare_gr`n' `lab`n''
}
save "FTE_gr_byCanton.dta", replace

keep if year == 2008

foreach var in FTE_gr6 FTE_gr0 FTE_gr3 FTE_gr7 FTE_gr5 FTE_gr4 FTE_gr9 {
	replace `var' = `var'*100
	replace `var' = round(`var', 0.01)
}

foreach var in FTE_share6 FTE_share0 FTE_share3 FTE_share7 FTE_share5 FTE_share4 FTE_share9 {
	replace `var' = `var'*100
	replace `var' = round(`var', 0.01)
}

gsort - FTEshare_gr6
gen sortvar =_n
replace sortvar = 0 if noga1 == 0
sort sortvar


* 1) FTE growth

order noga1 FTE_gr6 FTE_gr0 FTE_gr3 FTE_gr7 FTE_gr5 FTE_gr4 FTE_gr9
export excel noga1 FTE_gr6 FTE_gr0 FTE_gr3 FTE_gr7 FTE_gr5 FTE_gr4 FTE_gr9 ///
using "Tab_7)-FTE_development_byCanton.xlsx", sheet("FTE_gr") cell(A1) sheetmodify firstrow(varlabels)


* 2) FTE shares in 2008
order noga1 FTE_share6 FTE_share0 FTE_share3 FTE_share7 FTE_share5 FTE_share4 FTE_share9
export excel noga1 FTE_share6 FTE_share0 FTE_share3 FTE_share7 FTE_share5 FTE_share4 FTE_share9 ///
using "Tab_7)-FTE_development_byCanton.xlsx", sheet("FTE_share") cell(A1) sheetmodify firstrow(varlabels)


restore



						* * * * *  E N D  * * * * * * 
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
